﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient; 
using System.Data;
using System.Web;
using System.IO;
using System.Windows.Forms;


namespace libDAL
{
    public sealed class cls_DAL : IDisposable 
    {
        private SqlConnection sqlConnectLocal;
        private SqlDataAdapter[] sqlAdapterLocal;
        
        private SqlCommandBuilder sqlComBuilder = null ;

        private string strconnectionString = string.Empty;

        private string Get_connection_String()
        {
            return this.strconnectionString;
        }

        public cls_DAL(string strConString)
        {
            try
            {
              this.strconnectionString = strConString;
              sqlAdapterLocal = new SqlDataAdapter[1];
              connect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " cls_DAL"); 
            }
        }

        private void connect()
        {
            try
            {
                sqlConnectLocal = new SqlConnection(Get_connection_String());
            }
            catch (Exception ex)
            {
            }
       }
        //public Boolean set_Bool_updateBatch(ref DataSet dsDataSource, string strTableName)
        //{
        //    Boolean bool_Status = false;
        //    try
        //    {
        //        int intIndex = getDataAdapterIndex(strTableName);
        //        sqlAdapterLocal[intIndex].Update(dsDataSource, strTableName);
        //       // dsDataSource.AcceptChanges();
        //        bool_Status = true;
        //    }
        //    catch (Exception ex)
        //    {
        //       // MessageBox.Show(ex.Message + " set_updateBatch");
        //    }
        //    return bool_Status;
        //}

        public Boolean set_Bool_updateBatchNoAcceptChanges(ref DataSet dsDataSource, string strTableName)
        {
            Boolean bool_Status = false;
            try
            {
                int intIndex = getDataAdapterIndex(strTableName);
                sqlAdapterLocal[intIndex].Update(dsDataSource, strTableName);
                //  dsDataSource.AcceptChanges();
                bool_Status = true;
            }
            catch (Exception ex)
            {
                bool_Status = false;
               MessageBox.Show(ex.Message + " set_updateBatch");
            }
            return bool_Status;
        }

        public SqlConnection Get_Connection()
        {
            try
            {
                if (sqlConnectLocal.State == ConnectionState.Closed)
                {
                    sqlConnectLocal.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " Get_Connection"); 
            }
            return sqlConnectLocal;
        }

        public void Close_Connection()
        {
            try
            {
                if (sqlConnectLocal.State != ConnectionState.Closed)
                {
                    sqlConnectLocal.Close();
                }
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.Message + " Get_Connection"); 
            }
        }

               
        public bool set_updateBatch(ref DataSet dsDataSource, string strTableName)
        {
            bool bolStatus = false;
            try
            {
                int intIndex = getDataAdapterIndex(strTableName);
                sqlAdapterLocal[intIndex].Update(dsDataSource, strTableName);
                dsDataSource.AcceptChanges();
                bolStatus = true;
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_updateBatch"); 
            }
            return bolStatus;
        }

        public SqlDataReader get_OneRow_DataReader(string strQuery)
        {

            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand(strQuery, Get_Connection());
                myCommand.CommandTimeout = 0;
                myReader = myCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
               MessageBox.Show(ex.Message + " get_OneRow");
            }
            return myReader;
            myReader.Close();
        }

        private bool set_UseIndexedDataAdapter(int intIndex, SqlCommand cmd, ref DataSet dsData, string strTableName)
        {
            bool bolStatus = false;
            try
            {
                sqlAdapterLocal[intIndex] = new SqlDataAdapter(cmd);
                sqlAdapterLocal[intIndex].SelectCommand.CommandTimeout = 0;
                sqlComBuilder = new SqlCommandBuilder(sqlAdapterLocal[intIndex]);
                sqlAdapterLocal[intIndex].FillSchema(dsData, SchemaType.Mapped);
                sqlAdapterLocal[intIndex].Fill(dsData, strTableName);
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_UseIndexedDataAdapter"); 
            }
            return bolStatus;
        }

        private SqlDataAdapter get_SqlDataAdapterAndFillData(ref SqlCommand cmd, ref DataSet dsData, string strTableName)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter();
            try 
            {
                sqlda = new SqlDataAdapter(cmd);
                sqlda.SelectCommand.CommandTimeout = 0;
                sqlda.FillSchema(dsData, SchemaType.Mapped);
                sqlComBuilder = new SqlCommandBuilder(sqlda);
                sqlda.Fill(dsData, strTableName);
            }
            catch (Exception ex)
            {
                sqlda = null;
                MessageBox.Show(ex.Message + " get_SqlDataAdapterAndFillData"); 
            }

            return sqlda;
        }

        public int get_AdapterIndex()
        {
           return sqlAdapterLocal.Length + 1;
        }

        private bool set_AdjustAdapterIndex(int intIndex, SqlDataAdapter sqlDa)
        {
            bool bolStatus = false;
            try
            {
                if (intIndex == 0)
                {
                    sqlAdapterLocal[intIndex] = sqlDa;
                    bolStatus = true;
                }
                else
                {
                    Array.Resize(ref sqlAdapterLocal, intIndex + 1);
                    sqlAdapterLocal[intIndex] = sqlDa;
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_AdjustAdapterIndex"); 
            }

            return bolStatus;
        }

         public Boolean set_DataWCardinality_ds(string strQuery, ref DataSet dsData, string strTableName)
        {
            DataTable dtData = new DataTable();
            Boolean boolStatus = false;
            try
            {
                if (bool_get_checkSqlDataAdapterIndex(strTableName) == true)
                {
                    SqlDataAdapter sqlDa;
                    int getIndex = getDataAdapterIndex(strTableName);
                    //stablish sql command query and connection;
                    SqlCommand cmd = new SqlCommand(strQuery, Get_Connection());
                    //adjust the adapter index size and fill records
                    dsData = new DataSet();
                    sqlDa = get_SqlDataAdapterAndFillData(ref cmd, ref dsData, strTableName);
                    if (sqlDa != null)
                    {
                        if (set_AdjustAdapterIndex(getIndex, sqlDa) == true)
                        {
                            boolStatus = true;
                        }
                        else
                        {
                            boolStatus = false;
                        }
                    }
                    else
                    {
                        boolStatus = false;
                    }
                }
                else
                {
                    //Get_Connection()
                    int intIndex = getDataAdapterIndex(strTableName);
                    SqlCommand cmd = new SqlCommand(strQuery, Get_Connection());
                    if (set_UseIndexedDataAdapter(intIndex, cmd, ref dsData, strTableName) == true)
                    {boolStatus = true;}
                    else{boolStatus = false;}
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_DataWCardinality_ds"); 
                
            }
            return boolStatus;
         }

         public bool set_ExecuteStoredProc(ref SqlCommand sqlExecuteCommand)
         {
             bool bolStatus = false;
             try
             {
                 sqlExecuteCommand.ExecuteNonQuery();
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message + " set_ExecuteStoredProc");
             }
             return bolStatus;
         }

         public SqlCommand get_CommandStoredProc(string strStoredProcName)
         {
             SqlCommand cmd = new SqlCommand();
             try
             {
                 cmd = new SqlCommand(strStoredProcName, Get_Connection());
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.CommandTimeout = 0;
                 return cmd;
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message + " set_CommandNonQueryStoredProc");
             }
             return cmd;
         }

        public DataTable get_TableDetail(string strQuery)
        {
            DataTable dt = new DataTable();
            try
            {
                if (clsGenFunction.get_checkStringNull(strQuery) != string.Empty)
                {
                    SqlCommand cmd = new SqlCommand(strQuery, Get_Connection());

                    SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
                    sqlda.SelectCommand.CommandTimeout = 0;
                    sqlda.Fill(dt);
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_TableDetail"); 
            }
            return dt;
        
        }

        public void set_DeleteCommand(string strQuery)
        {
            try
            {
                SqlCommand sqlcomExec = new SqlCommand(strQuery,  Get_Connection());
                sqlcomExec.CommandTimeout = 0; 
                sqlcomExec.ExecuteNonQuery();
                                
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message + " set_DeleteCommand"); 
            }
        }

        public Boolean set_GenQueryExNonQuery(string strQuery)
        {
            Boolean bool_ExecuteStatus = false;
            try
            {
                SqlCommand sqlcomExec = new SqlCommand(strQuery, Get_Connection());
                sqlcomExec.CommandTimeout = 0;
                int numberOfRecords = sqlcomExec.ExecuteNonQuery();
                bool_ExecuteStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_GenQueryExNonQuery");
            }
            return bool_ExecuteStatus; 
        }

        public bool get_CheckAnythingExists(string strQuery)
        {
            bool bolStatus = false;
            try
            {
                SqlCommand sqlcomExec = new SqlCommand(strQuery, Get_Connection());
                SqlDataReader reader = sqlcomExec.ExecuteReader();

                if (reader.HasRows)
                {
                    bolStatus = true;
                }

                if (reader.IsClosed == false)
                {

                    reader.Close();
                }
            }
            catch (Exception ex)
            { 
                MessageBox.Show (ex.Message + "DAL_get_CheckIDExists");
            }
            return bolStatus;
        }

        public Boolean set_GenQueryExNonQueryStoredProc(ref SqlCommand sqlcomExec, string strStoredProcName)
        {
            Boolean bool_ExecuteStatus = false;
            try
            {
                sqlcomExec.CommandText = strStoredProcName;
                sqlcomExec.CommandTimeout = 0;
                sqlcomExec.ExecuteNonQuery();
                sqlcomExec.Dispose();
                bool_ExecuteStatus = true;
            }
            catch (Exception ex)
            {
                 MessageBox.Show(ex.Message + " set_GenQueryExNonQuery");
            }
            return bool_ExecuteStatus;
        }
        
        public void set_InsertCommandTabled(string strQuery,string strTableName)
        {
            try
            {
                int intIndex = getDataAdapterIndex(strTableName);
                sqlAdapterLocal[intIndex].InsertCommand = Get_Connection().CreateCommand();
                sqlAdapterLocal[intIndex].InsertCommand.CommandTimeout = 0;
                sqlAdapterLocal[intIndex].InsertCommand.CommandText = strQuery;
                sqlAdapterLocal[intIndex].InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_InsertCommandTabled"); 
            }
        }

      

        private Boolean bool_get_checkSqlDataAdapterIndex(string strTableName)
        {
            Boolean bool_Check = false;
            try 
            {
                bool_Check = false;
                if (getDataAdapterIndex(strTableName) > sqlAdapterLocal.Length - 1  || getDataAdapterIndex(strTableName) == 0)
                {
                    bool_Check = true;
                }
            }
            catch (Exception ex)
            {
                
            }
            return bool_Check;
        }

        

        public Boolean Update_batch_ds(ref DataSet dsDataSource, string strTableName)
        {
            Boolean bool_status = false;
            try
            {
                   int intIndex = getDataAdapterIndex(strTableName);
                   sqlAdapterLocal[intIndex].Update(dsDataSource, strTableName);
                   dsDataSource.AcceptChanges();
                   bool_status = true;

                //dsDataSource.Tables[strTableName].Rows.Count 
            }
            catch (Exception ex)
            {
                bool_status = false;
                MessageBox.Show(ex.Message + " Update_batch_ds"); 
            }
            return bool_status;
        }

        public string get_OneKey(string strQuery,string strDataType)
        {
            string strKey = string.Empty; 
            try
            {
                if (strQuery.Trim() != string.Empty)
                {
                    SqlCommand myCommand = new SqlCommand(strQuery, Get_Connection());
                    myCommand.CommandTimeout = 0;
                    SqlDataReader myReader = myCommand.ExecuteReader();
                    // Always call Read before accessing data.
                    while (myReader.Read())
                    {
                        if (myReader[0] != DBNull.Value)
                        {
                            strKey = myReader[0].ToString(); ;
                        }
                    }
                    if (strKey == string.Empty)
                    {
                        if (strDataType != "String")
                        {
                            strKey = "0";
                        }
                        else if (strDataType == "String")
                        {
                            strKey = string.Empty;
                        }
                    }
                    myReader.Close();
                }
            }
                // always call Close when done reading.
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_OneKey"); 
            }
            return strKey;
        }

        public DateTime get_ServerDate_ddMMyyyy(string strQuery)
        {
            DateTime strKey = DateTime.Now;
          
            try
            {
                SqlCommand myCommand = new SqlCommand(strQuery, Get_Connection());
                myCommand.CommandTimeout = 0;
                SqlDataReader myReader;
                myReader = myCommand.ExecuteReader();

                // Always call Read before accessing data.
                while (myReader.Read())
                {
                    strKey = Convert.ToDateTime(myReader.GetValue(0));
                }
                // always call Close when done reading.
                myReader.Close();
            }
            catch (Exception ex)
            {
              
              //  MessageBox.Show(ex.Message + " get_OneKey");
            }
            return strKey;
        }

        public SqlDataReader getLineRecord(string strQuery)
        {
            SqlDataReader myReader = null;
            try
            {
                SqlCommand myCommand = new SqlCommand(strQuery, Get_Connection());
                myCommand.CommandTimeout = 0;
                myReader = myCommand.ExecuteReader();

            }
            catch (Exception ex)
            {
              //  MessageBox.Show(ex.Message + " getLineRecord"); 
            }
            return myReader;
        }

        private int getDataAdapterIndex(string strTableName)
        {
            int intIndex = 0;
            try
            {
                string strDash = strTableName.Substring(strTableName.IndexOf("-"), strTableName.Length - strTableName.IndexOf("-"));
                intIndex = Math.Abs(Convert.ToInt16(strDash));
               
            }
            catch(Exception ex)
            {
              //  MessageBox.Show(ex.Message + " getDataAdapterIndex"); 
            }
            return intIndex;
        }

        public void Dispose()
        {

            //sqlConnectLocal;
            //sqlComBuilder.Dispose();
           Close_Connection();
           GC.Collect();
           // throw new NotImplementedException();
        }
    }
}
